package main

import (
	"encoding/json"
	"fmt"
	"log"
	"os"
	"sort"
	"strconv"

	"github.com/xuri/excelize/v2"
)

type Property struct {
	Name       string `json:"name"`
	Label      string `json:"label"`
	AccessMode int    `json:"accessMode"`
	Type       int    `json:"type"`
	Params     struct {
		DbNumber  string `json:"dbNumber"`
		DataType  int    `json:"dataType"`
		StartAddr string `json:"startAddr"`
	} `json:"params"`
	Decimals int         `json:"decimals"`
	Unit     string      `json:"unit"`
	Formula  string      `json:"formula"`
	WFormula string      `json:"wFormula"`
	Script   string      `json:"script"`
	Value    interface{} `json:"value"`
}

type Device struct {
	Index      int        `json:"index"`
	Name       string     `json:"name"`
	Label      string     `json:"label"`
	Type       int        `json:"type"`
	Properties []Property `json:"properties,omitempty"`
}

func main() {
	// 打开JSON文件
	jsonFile, err := os.Open("TSLModelS7Param.json")
	if err != nil {
		log.Fatalf("无法打开JSON文件: %v", err)
	}
	defer jsonFile.Close()

	// 解码JSON数据
	var devices map[string]Device
	err = json.NewDecoder(jsonFile).Decode(&devices)
	if err != nil {
		log.Fatalf("无法解析JSON文件: %v", err)
	}

	// 将设备按Index排序
	sortedDevices := make([]Device, 0, len(devices))
	for _, device := range devices {
		sortedDevices = append(sortedDevices, device)
	}
	sort.Slice(sortedDevices, func(i, j int) bool {
		return sortedDevices[i].Index < sortedDevices[j].Index
	})

	
	// 创建新的Excel文件
	f := excelize.NewFile()

	// 设置Sheet名称和表头
	sheetName := "西门子PLC-S7物模型"
	index,_ := f.NewSheet(sheetName)
	// 设置Sheet名称和表头
	headers := []string{"模型主键(Key)", "名称(Name)", "标签 (Label)", "类型(Type)", "属性名称(Property Name)", "属性标签(Property Label)", "读写类型(0:只读)(禁止修改)", "属性类型(0:uint32,1:int32,2:double,3:string)",
		"数据块号(I,Q,M,DB1,DB2)", "属性类型(0:uint8 1:int8 2:uint16 3:int16 4:uint32 5:int32 6:float 7:double 8:bool)", "PLC地址(0.0 0 10 100)", "小数位(Decimals)", "单位(Unit)", "读取计算公式(Formula)", "写入公式(禁止修改)", "脚本", "值"}
	for col, header := range headers {
		cell, _ := excelize.CoordinatesToCellName(col+1, 1)
		f.SetCellValue(sheetName, cell, header)
	}

	row := 2 // 从第二行开始写入数据

	// 写入设备数据
	for _, device := range sortedDevices {
		for _, prop := range device.Properties {
			f.SetCellValue(sheetName, fmt.Sprintf("A%d", row), device.Index)
			f.SetCellValue(sheetName, fmt.Sprintf("B%d", row), device.Name)
			f.SetCellValue(sheetName, fmt.Sprintf("C%d", row), device.Label)
			f.SetCellValue(sheetName, fmt.Sprintf("D%d", row), device.Type)
			f.SetCellValue(sheetName, fmt.Sprintf("E%d", row), prop.Name)
			f.SetCellValue(sheetName, fmt.Sprintf("F%d", row), prop.Label)
			f.SetCellValue(sheetName, fmt.Sprintf("G%d", row), prop.AccessMode)
			f.SetCellValue(sheetName, fmt.Sprintf("H%d", row), prop.Type)
			f.SetCellValue(sheetName, fmt.Sprintf("I%d", row), prop.Params.DbNumber)
			f.SetCellValue(sheetName, fmt.Sprintf("J%d", row), prop.Params.DataType)
			f.SetCellValue(sheetName, fmt.Sprintf("K%d", row), prop.Params.StartAddr)
			f.SetCellValue(sheetName, fmt.Sprintf("L%d", row), prop.Decimals)
			f.SetCellValue(sheetName, fmt.Sprintf("M%d", row), prop.Unit)
			f.SetCellValue(sheetName, fmt.Sprintf("N%d", row), prop.Formula)
			f.SetCellValue(sheetName, fmt.Sprintf("O%d", row), prop.WFormula)
			f.SetCellValue(sheetName, fmt.Sprintf("P%d", row), prop.Script)
			f.SetCellValue(sheetName, fmt.Sprintf("Q%d", row), prop.Value)
			row++
		}
	}

	// 设置活动工作表为新创建的工作表
	f.SetActiveSheet(index)
    // 删除默认的Sheet
    f.DeleteSheet("Sheet1")
	// 保存Excel文件
	if err := f.SaveAs("merged.xlsx"); err != nil {
		log.Fatalf("无法保存Excel文件: %v", err)
	}

	fmt.Println("合并成功，Excel文件已保存到 merged.xlsx")
}

func atoi(s string) int {
	i, err := strconv.Atoi(s)
	if err != nil {
		return 0
	}
	return i
}
